﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace XT.Exam.Common
{
    public class ExcelHelper
    {
        //Excel数据转List<T>
        public static IList<T> ReadExcelToEntityList<T>(string filePath) where T : class, new()
        {
            DataTable tbl = ReadExcelToDataTable(filePath);//读取Excel数据到DataTable

            IList<T> list = DataTableToList<T>(tbl);

            return list;

        }

        //Excel数据转DataTable 使用的oledb读取方式
        public static DataTable ReadExcelToDataTable(string filePath)
        {

            if (filePath == string.Empty) throw new ArgumentNullException("路径参数不能为空");
            string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Persist Security Info=False;Data Source=" + filePath + "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
            OleDbDataAdapter adapter = new OleDbDataAdapter("select * From[Sheet1$]", ConnectionString); //默认读取的Sheet1,你也可以把它封装变量,动态读取你的Sheet工作表
            DataTable table = new DataTable("TempTable");
            adapter.Fill(table);
            return table;
        }


        //DataTable转List<T>
        public static List<T> DataTableToList<T>(DataTable dt) where T : class, new()
        {

            if (dt == null) return null;

            List<T> list = new List<T>();

            //遍历DataTable中所有的数据行 
            foreach (DataRow dr in dt.Rows)
            {
                T t = new T();

                PropertyInfo[] propertys = t.GetType().GetProperties();

                foreach (PropertyInfo pro in propertys)
                {
                    //检查DataTable是否包含此列（列名==对象的属性名）   
                    if (dt.Columns.Contains(pro.Name))
                    {
                        object value = dr[pro.Name];

                        value = Convert.ChangeType(value, pro.PropertyType);//强制转换类型

                        //如果非空，则赋给对象的属性  PropertyInfo
                        if (value != DBNull.Value)
                        {
                            pro.SetValue(t, value, null);
                        }
                    }
                }
                //对象添加到泛型集合中 
                list.Add(t);
            }

            return list;

        }
    }
}
